package com.imooc.dao;

import java.sql.*;
import java.sql.Date;
import java.util.*;

import com.imooc.db.DBUtil;
import com.imooc.model.Goddess;

public class GoddessDao {
	public void addGoddess(Goddess g) throws Exception {
		Connection connect = DBUtil.getConnection();
		String sql = " " + "insert into imooc.imooc_goddess" + "(user_name,sex,age,birthday,email,mobile,"
				+ "create_user,create_date,update_user,update_date,isdel)" + "values("
				+ "?,?,?,?,?,?,?,current_date(),?,current_date(),?)";
		PreparedStatement ptmt = connect.prepareStatement(sql);
		ptmt.setString(1, g.getUser_name());
		ptmt.setInt(2, g.getSex());
		ptmt.setInt(3, g.getAge());
		ptmt.setDate(4, new Date(g.getBirthday().getTime()));
		ptmt.setString(5, g.getEmail());
		ptmt.setString(6, g.getMobile());
		ptmt.setString(7, g.getCreate_user());
		ptmt.setString(8, g.getUpdate_user());
		ptmt.setInt(9, g.getIsdel());
		ptmt.execute();
	}

	public void deleteGoddess(Integer id) throws Exception {
		Connection connect = DBUtil.getConnection();
		String sql = " " + " delete from imooc.imooc_goddess " + " where id=? ";
		PreparedStatement ptmt = connect.prepareStatement(sql);
		ptmt.setInt(1, id);
		ptmt.execute();
	}

	public void updateGoddess(Goddess g) throws SQLException {
		Connection connect = DBUtil.getConnection();
		String sql = " " + " update  imooc.imooc_goddess " + " set user_name=?,sex=?,age=?,birthday=?,email=?,mobile=?, "
				+ " update_user=?,update_date=current_date(),isdel=? " + " where id=? ";
		PreparedStatement ptmt = connect.prepareStatement(sql);
		ptmt.setString(1, g.getUser_name());
		ptmt.setInt(2, g.getSex());
		ptmt.setInt(3, g.getAge());
        ptmt.setDate(4, new Date(g.getBirthday().getTime()));
		ptmt.setString(5, g.getEmail());
		ptmt.setString(6, g.getMobile());
		ptmt.setString(7, g.getUpdate_user());
		ptmt.setInt(8, g.getIsdel());
		ptmt.setInt(9, g.getId());
		ptmt.execute();

	}

	public List<Goddess> query() throws Exception {
		Connection connect = DBUtil.getConnection();
		Statement state = connect.createStatement();
		ResultSet rs = state.executeQuery("select * from imooc.imooc_goddess");
		List<Goddess> gs = new ArrayList<Goddess>();
		Goddess g = null;
		while (rs.next()) {
			g = new Goddess();
			g.setUser_name(rs.getString("user_name"));
			g.setAge(rs.getInt("Age"));
			gs.add(g);

		}
		return gs;
	}

	public Goddess getQuery(Integer id) throws SQLException {
		Goddess g = null;
		Connection connect = DBUtil.getConnection();
		
		String sql = " " + " select * from imooc.imooc_goddess " + " where id=? ";
		PreparedStatement ptmt = connect.prepareStatement(sql);
		ptmt.setInt(1, id);
		ResultSet rs = ptmt.executeQuery();
		while (rs.next()) {
			g=new Goddess();
			g.setId(rs.getInt("id"));
			g.setUser_name(rs.getString("user_name"));
			g.setSex(rs.getInt("sex"));
			g.setAge(rs.getInt("age"));
			g.setBirthday(rs.getDate("birthday"));
			g.setEmail(rs.getString("email"));
			g.setMobile(rs.getString("mobile"));
			g.setCreate_user(rs.getString("create_user"));
			g.setUpdate_user(rs.getString("update_user"));
			g.setCreate_date(rs.getDate("create_date"));
			g.setUpdate_date(rs.getDate("update_date "));
			g.setIsdel(rs.getInt("isdel"));
		}
		return g;
	}
}
